package com.ats.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.ats.dao.JobRecordDao_xh;
import com.ats.model.JobRecord;
import com.ats.util.JDBCUtil;

import net.sf.json.JSONArray;
/**
 * 作业记录功能的DAO层实现类
 * @author xh
 *
 */
public class JobRecordDaoImpl_xh implements JobRecordDao_xh{

	@Override
	public JSONArray findJobRecordBySid(int sid,int start,int rows) {
		// TODO Auto-generated method stub
		JSONArray jso = new JSONArray();
		StringBuffer  sql = new StringBuffer();
		sql.append("select ats_job_record.jrid,ats_job_record.beginTime,ats_job.jname,ats_job_state.jsname, ");
		sql.append("ats_job_record.grade,ats_teacher.`name` tname ");
		sql.append("from ats_job_record ");
		sql.append("inner join ats_job on ats_job_record.jid=ats_job.jid ");
		sql.append("inner join ats_job_state on ats_job_record.jsid=ats_job_state.jsid ");
		sql.append("inner join ats_teacher on ats_teacher.tid=ats_job_record.tid ");
		sql.append("where sid= ? and ats_job_state.jsname='未完成' limit ?,?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,sid);
			pst.setInt(2,start);
			pst.setInt(3,rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				JobRecord jr = new JobRecord();
				jr.setJrid(rs.getInt("jrid"));
				jr.setBeginTime(rs.getString("beginTime"));
				jr.setJname(rs.getString("jname"));
				jr.setJsname(rs.getString("jsname"));
				jr.setGrade(rs.getInt("grade"));
				jr.setTname(rs.getString("tname"));
				jso.add(jr);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jso;
	}

	@Override
	public int findAllJobRecordBySid(int sid) {
		// TODO Auto-generated method stub
		int rcount = 0;
		StringBuffer  sql = new StringBuffer();
		sql.append("select count(*) rcount from ats_job_record  ");
		sql.append("inner join ats_job on ats_job_record.jid=ats_job.jid ");
		sql.append("inner join ats_job_state on ats_job_record.jsid=ats_job_state.jsid ");
		sql.append("inner join ats_teacher on ats_teacher.tid=ats_job_record.tid ");
		sql.append("where sid= ? and ats_job_state.jsname='未完成' ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,sid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				rcount=rs.getInt("rcount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rcount;
	}

	@Override
	public boolean updateJobRecordBySid(String endTime,int sid,int jrid,String answer) {
		// TODO Auto-generated method stub
		boolean flag =false;
		StringBuffer  sql = new StringBuffer();
		sql.append("update ats_job_record set endTime= ?,jsid=2,answer=? where sid = ? and jrid = ? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, endTime);
			pst.setString(2, answer);
			pst.setInt(3, sid);
			pst.setInt(4, jrid);
			int i =pst.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public JSONArray findJobRecordByTid(int tid, int start, int rows) {
		// TODO Auto-generated method stub
		JSONArray jso = new JSONArray();
		StringBuffer  sql = new StringBuffer();
		sql.append("select ats_job_record.jrid,ats_job_record.endTime,ats_job.jname,ats_job_state.jsname,  ");
		sql.append("ats_job_record.grade,ats_job_record.answer,ats_student.`name` sname  ");
		sql.append("from ats_job_record ");
		sql.append("inner join ats_job on ats_job_record.jid=ats_job.jid  ");
		sql.append("inner join ats_job_state on ats_job_record.jsid=ats_job_state.jsid  ");
		sql.append("inner join ats_student on ats_student.sid=ats_job_record.sid  ");
		sql.append("where tid= ? and ats_job_state.jsname='已完成' limit ?,?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,tid);
			pst.setInt(2,start);
			pst.setInt(3,rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				JobRecord jr = new JobRecord();
				jr.setJrid(rs.getInt("jrid"));
				jr.setEndTime(rs.getString("endTime"));
				jr.setJname(rs.getString("jname"));
				jr.setJsname(rs.getString("jsname"));
				jr.setAnswer(rs.getString("answer"));
				jr.setGrade(rs.getInt("grade"));
				jr.setSname(rs.getString("sname"));
				jso.add(jr);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jso;
	}

	@Override
	public int findAllJobRecordByTid(int tid) {
		// TODO Auto-generated method stub
		int rcount = 0;
		StringBuffer  sql = new StringBuffer();
		sql.append("select count(*) rcount from ats_job_record   ");
		sql.append("inner join ats_job on ats_job_record.jid=ats_job.jid ");
		sql.append("inner join ats_job_state on ats_job_record.jsid=ats_job_state.jsid  ");
		sql.append("inner join ats_student on ats_student.sid=ats_job_record.sid  ");
		sql.append("where tid= ? and ats_job_state.jsname='已完成' and grade=0  ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,tid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				rcount=rs.getInt("rcount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rcount;
	}

	@Override
	public boolean updateJobRecordByTidAndJrid( int grade,int jrid, int tid) {
		// TODO Auto-generated method stub
		boolean flag =false;
		StringBuffer  sql = new StringBuffer();
		sql.append("update ats_job_record set grade=? where jrid=? and tid =?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, grade);
			pst.setInt(2, jrid);
			pst.setInt(3, tid);
			int i =pst.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public JSONArray findAllJobRecordBySid(int sid, int start, int rows) {
		// TODO Auto-generated method stub
		JSONArray jso = new JSONArray();
		StringBuffer  sql = new StringBuffer();
		sql.append("select ats_job_record.jrid,ats_job_record.beginTime,ats_job_record.endTime,ats_job_record.answer, ");
		sql.append("ats_job.jname,ats_job_state.jsname, ");
		sql.append("ats_job_record.grade,ats_teacher.`name` allname ");
		sql.append("from ats_job_record ");
		sql.append("inner join ats_job on ats_job_record.jid=ats_job.jid  ");
		sql.append("inner join ats_job_state on ats_job_record.jsid=ats_job_state.jsid ");
		sql.append("inner join ats_teacher on ats_teacher.tid=ats_job_record.tid ");
		sql.append("where sid= ?  limit ?,?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,sid);
			pst.setInt(2,start);
			pst.setInt(3,rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				JobRecord jr = new JobRecord();
				jr.setJrid(rs.getInt("jrid"));
				jr.setBeginTime(rs.getString("beginTime"));
				jr.setEndTime(rs.getString("endTime"));
				jr.setJname(rs.getString("jname"));
				jr.setJsname(rs.getString("jsname"));
				jr.setAnswer(rs.getString("answer"));
				jr.setGrade(rs.getInt("grade"));
				jr.setName(rs.getString("allname"));
				jso.add(jr);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jso;
	}

	@Override
	public int findAllRecordBySid(int sid) {
		// TODO Auto-generated method stub
		int rcount = 0;
		StringBuffer  sql = new StringBuffer();
		sql.append("select count(*) rcount from ats_job_record  ");
		sql.append("inner join ats_job on ats_job_record.jid=ats_job.jid ");
		sql.append("inner join ats_job_state on ats_job_record.jsid=ats_job_state.jsid ");
		sql.append("inner join ats_teacher on ats_teacher.tid=ats_job_record.tid ");
		sql.append("where sid= ? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,sid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				rcount=rs.getInt("rcount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rcount;
	}

	@Override
	public JSONArray findAllJobRecordByTid(int tid, int start, int rows) {
		// TODO Auto-generated method stub
		JSONArray jso = new JSONArray();
		StringBuffer  sql = new StringBuffer();
		sql.append("select ats_job_record.jrid,ats_job_record.beginTime,ats_job_record.endTime,ats_job_record.answer,  ");
		sql.append("ats_job.jname,ats_job_state.jsname,  ");
		sql.append("ats_job_record.grade,ats_student.`name` allname   ");
		sql.append("from ats_job_record   ");
		sql.append("inner join ats_job on ats_job_record.jid=ats_job.jid ");
		sql.append("inner join ats_job_state on ats_job_record.jsid=ats_job_state.jsid   ");
		sql.append("inner join ats_student on ats_student.sid=ats_job_record.sid  ");
		sql.append("where tid= ? limit ?,?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,tid);
			pst.setInt(2,start);
			pst.setInt(3,rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				JobRecord jr = new JobRecord();
				jr.setJrid(rs.getInt("jrid"));
				jr.setBeginTime(rs.getString("beginTime"));
				jr.setEndTime(rs.getString("endTime"));
				jr.setJname(rs.getString("jname"));
				jr.setJsname(rs.getString("jsname"));
				jr.setAnswer(rs.getString("answer"));
				jr.setGrade(rs.getInt("grade"));
				jr.setName(rs.getString("allname"));
				jso.add(jr);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jso;
	}

	@Override
	public int findAllRecordByTid(int tid) {
		// TODO Auto-generated method stub
		int rcount = 0;
		StringBuffer  sql = new StringBuffer();
		sql.append("select count(*) rcount from ats_job_record   ");
		sql.append("inner join ats_job on ats_job_record.jid=ats_job.jid ");
		sql.append("inner join ats_job_state on ats_job_record.jsid=ats_job_state.jsid  ");
		sql.append("inner join ats_student on ats_student.sid=ats_job_record.sid  ");
		sql.append("where tid= ?  ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,tid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				rcount=rs.getInt("rcount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rcount;
	}

	@Override
	public JSONArray findAllJobRecordBySidAndJsid(int sid,int jsid) {
		// TODO Auto-generated method stub
		JSONArray jso = new JSONArray();
		StringBuffer  sql = new StringBuffer();
		sql.append("select ats_job_record.jrid,ats_job_record.beginTime,ats_job_record.endTime, ");
		sql.append("ats_job.jname,ats_job_state.jsname, ");
		sql.append("ats_job_record.grade,ats_teacher.`name` allname ");
		sql.append("from ats_job_record ");
		sql.append("inner join ats_job on ats_job_record.jid=ats_job.jid  ");
		sql.append("inner join ats_job_state on ats_job_record.jsid=ats_job_state.jsid ");
		sql.append("inner join ats_teacher on ats_teacher.tid=ats_job_record.tid ");
		sql.append("where sid= ? and ats_job_state.jsid=?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,sid);
			pst.setInt(2,jsid);		
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				JobRecord jr = new JobRecord();
				jr.setJrid(rs.getInt("jrid"));
				jr.setBeginTime(rs.getString("beginTime"));
				jr.setEndTime(rs.getString("endTime"));
				jr.setJname(rs.getString("jname"));
				jr.setJsname(rs.getString("jsname"));
				jr.setGrade(rs.getInt("grade"));
				jr.setName(rs.getString("allname"));
				jso.add(jr);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jso;
	}
/**
 * 
 * @param sid
 * @param jsid
 * @return
 
	@Override
	public int findAllRecordBySidAndJsid(int sid, int jsid) {
		// TODO Auto-generated method stub
		int rcount = 0;
		StringBuffer  sql = new StringBuffer();
		sql.append("select count(*) rcount from ats_job_record  ");
		sql.append("inner join ats_job on ats_job_record.jid=ats_job.jid ");
		sql.append("inner join ats_job_state on ats_job_record.jsid=ats_job_state.jsid ");
		sql.append("inner join ats_teacher on ats_teacher.tid=ats_job_record.tid ");
		sql.append("where sid= ? and ats_job_state.jsid=?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,sid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				rcount=rs.getInt("rcount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rcount;
	}
*/
	@Override
	public JSONArray findAllJobRecordByTidAndJsid(int tid, int jsid) {
		// TODO Auto-generated method stub
		JSONArray jso = new JSONArray();
		StringBuffer  sql = new StringBuffer();
		sql.append("select ats_job_record.jrid,ats_job_record.beginTime,ats_job_record.endTime,  ");
		sql.append("ats_job.jname,ats_job_state.jsname,  ");
		sql.append("ats_job_record.grade,ats_student.`name` allname ");
		sql.append("from ats_job_record   ");
		sql.append("inner join ats_job on ats_job_record.jid=ats_job.jid ");
		sql.append("inner join ats_job_state on ats_job_record.jsid=ats_job_state.jsid   ");
		sql.append("inner join ats_student on ats_student.sid=ats_job_record.sid  ");
		sql.append("where tid= ? and ats_job_state.jsid=? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,tid);
			pst.setInt(2,jsid);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				JobRecord jr = new JobRecord();
				jr.setJrid(rs.getInt("jrid"));
				jr.setBeginTime(rs.getString("beginTime"));
				jr.setEndTime(rs.getString("endTime"));
				jr.setJname(rs.getString("jname"));
				jr.setJsname(rs.getString("jsname"));
				jr.setGrade(rs.getInt("grade"));
				jr.setName(rs.getString("allname"));
				jso.add(jr);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jso;
	}
	/**
	@Override
	
	 * 
	 * @param tid
	 * @param jsid
	 * @return
	 
	public int findAllRecordByTidAndJsid(int tid, int jsid) {
		// TODO Auto-generated method stub
		int rcount = 0;
		StringBuffer  sql = new StringBuffer();
		sql.append("select count(*) rcount from ats_job_record   ");
		sql.append("inner join ats_job on ats_job_record.jid=ats_job.jid ");
		sql.append("inner join ats_job_state on ats_job_record.jsid=ats_job_state.jsid  ");
		sql.append("inner join ats_student on ats_student.sid=ats_job_record.sid  ");
		sql.append("where tid= ? and ats_job_state.jsid=? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,tid);
			pst.setInt(2,jsid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				rcount=rs.getInt("rcount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rcount;
	}
*/
}
